#绘制折线图
library(tidyverse)
library(lubridate)

conn <- src_sqlite("~/Documents/rockontrol/合肥项目数据/合肥市项目.db")

begin <- "2019-07-01"
end <- "2019-07-31"

town <-  "包河区"

dataset <- tbl(conn,"小时数据") %>%
  inner_join(tbl(conn,"站点信息"),by  =c("站点名称"="站点")) %>%
  filter(类型 == "国控站",日期>= begin,日期<= end)  %>% collect()

avg <- dataset %>%  mutate(时间  = hour(日期),flag= "合肥市均值")%>% 
  group_by(flag,时间) %>% 
  summarize(PM2.5浓度 = mean(PM2.5浓度,na.rm = TRUE),PM10浓度 = mean(PM10浓度,na.rm= TRUE))
  

dataset %>%filter(区县 == town) %>% mutate(时间  = hour(日期),flag = 站点名称) %>% 
  group_by(flag,时间) %>% 
  summarize(PM2.5浓度 = mean(PM2.5浓度,na.rm = TRUE),PM10浓度 = mean(PM10浓度,na.rm= TRUE))%>%
  union_all(avg) %>%
  mutate("PM[10]/PM[2.5]" =PM10浓度/PM2.5浓度 )%>%
  select ("站点" = flag,时间,"PM[10]~ug/m^3" = PM10浓度,"PM[2.5]~ug/m^3" = PM2.5浓度,`PM[10]/PM[2.5]`)%>%
  gather(key = 污染物,value = 浓度,...= 3:5)%>%
  ggplot(aes(x = 时间,y = 浓度,group = 站点,color= 站点)) +
  geom_line(size =1)+
  scale_x_continuous(breaks =seq(1,24,2))+
  labs(x= "小时",y= "") + 
  facet_grid(污染物 ~.,scales = "free_y",labeller = label_parsed)+
  theme_bw() + theme(  plot.title =   element_text(hjust = 0.5),
                     axis.text.x=element_text(size = 15,vjust = 0.5),
                     axis.title.y = element_text(size = 15),
                     axis.text.y = element_text(size = 15),
                     strip.text = element_text(size= 15),legend.text.align = 0,legend.title = element_blank(),
                     legend.text = element_text(size = 15))



DBI::dbDisconnect(conn$con)


